package com.itqn.dao.Impl;

import cn.hutool.core.util.StrUtil;
import com.itqn.dao.EmployeeDao;
import com.itqn.pojo.Employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public Employee login(Employee employee) throws Exception {
        BaseDao baseDao = new BaseDao();
        Employee employee1 = null;
        String sql = "SELECT id,name,username,password,status FROM employee where username=? and password=?";
        rs = baseDao.getlist(sql, new Object[]{employee.getUsername(), employee.getPassword()});
        if (rs.next()) {
            employee1 = new Employee();
            employee1.setId(rs.getLong("id"));
            employee1.setStatus(rs.getInt("status"));
            employee1.setUsername(rs.getString("username"));
            employee1.setName(rs.getString("name"));
            employee1.setPassword(rs.getString("password"));
        }
        return employee1;
    }

    @Override
    public int UpPassword(Employee employee) throws Exception {
        int result=0;
        BaseDao baseDao=new BaseDao();
        String sql="update employee set password = ? where id=?";
        result=baseDao.update(sql,new Object[]{employee.getPassword(),employee.getId()});
        return result;
    }


    @Override
    public int deleteEmployee(int id) {
        return 0;
    }

    @Override
    public int AddEmployee(Employee employee) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "insert into employee (name, username, password, phone, sex, id_number, status) values (?,?,?,?,?,?,1)";
        result = baseDao.update(sql, new Object[]{employee.getName(), employee.getUsername(), employee.getPassword(), employee.getPhone(), employee.getSex(), employee.getIdNumber()});
        return result;
    }

    @Override
    public Employee findEmployeeById(int id) throws Exception {
        BaseDao baseDao = new BaseDao();
        Employee employee = null;
        String sql = "SELECT name,password,username,sex,phone,id_number,status FROM employee WHERE id = ?";
        ResultSet set = baseDao.getlist(sql, new Object[]{id});
        if (set.next()) {
            employee = new Employee();
            employee.setName(set.getString("name"));
            employee.setPassword(set.getString("password"));
            employee.setUsername(set.getString("username"));
            employee.setSex(set.getString("sex"));
            employee.setPhone(set.getString("phone"));
            employee.setIdNumber(set.getString("id_number"));
            employee.setStatus(set.getInt("status"));
        }
        return employee;
    }

    @Override
    public int updateEmployee(Employee employee) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "update employee set username = ?, name = ? ,phone = ?,sex = ?, id_number = ? where id = ?";
        result = baseDao.update(sql, new Object[]{employee.getUsername(), employee.getName(), employee.getPhone(), employee.getSex(), employee.getIdNumber(), employee.getId()});
        return result;
    }

    @Override
    public Integer disable(Employee employee) throws Exception {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "update employee set status = ? where id=?";
        result = baseDao.update(sql, new Object[]{employee.getStatus(), employee.getId()});
        return result;
    }

    @Override
    public List<Employee> query(String name, Integer pageSize, Integer page) throws Exception {
        List<Employee> list = new ArrayList<>();
        Employee employee = null;
        BaseDao baseDao = new BaseDao();
        StringBuilder sb = new StringBuilder();
        sb.append("select id,name,username,sex,phone,id_number,status from employee ");
        Boolean flag = false;
        if (StrUtil.isNotBlank(name)) {
            flag = true;
            sb.append("where name LIKE CONCAT('%',?,'%') ");
        }
        Boolean isPage = page != null && pageSize != null;
        if (isPage) {
            sb.append("limit ?, ?");
        }

        if (flag) {
            rs = baseDao.getlist(sb.toString(), new Object[]{name, (page - 1) * pageSize, pageSize});
        } else {
            rs = baseDao.getlist(sb.toString(), new Object[]{(page - 1) * pageSize, pageSize});
        }
        if (rs == null) {
            return Collections.emptyList();
        }

        while (rs.next()) {
            employee = new Employee();
            employee.setId(rs.getLong("id"));
            employee.setName(rs.getString("name"));
            employee.setUsername(rs.getString("username"));
            employee.setSex(rs.getString("sex"));
            employee.setPhone(rs.getString("phone"));
            employee.setIdNumber(rs.getString("id_number"));
            employee.setStatus(rs.getInt("status"));
            list.add(employee);
        }
        return list;
    }

    @Override
    public Integer totalCount(String name, Integer pageSize, Integer page) throws Exception {
        BaseDao baseDao = new BaseDao();
        //编写sql语句
        StringBuilder sb = new StringBuilder();
        sb.append("select count(id) from employee ");
        if (StrUtil.isNotBlank(name)) {
            sb.append("where name LIKE CONCAT('%',?,'%') ");
        }
        if (StrUtil.isNotBlank(name)) {
            rs = baseDao.getlist(sb.toString(), new Object[]{name});
        } else {
            rs = baseDao.getlist(sb.toString(), new Object[]{});
        }

        int number = 0;
        if (rs.next()) {
            number = rs.getInt(1);
        }
        // number是总条数
        return number;
    }

    @Override
    public void updatestatic(Employee employee) throws Exception {
        BaseDao baseDao = new BaseDao();
        String sql = "UPDATE employee set `status`=? where id=?";
        baseDao.update(sql, new Object[]{employee.getStatus(), employee.getId()});
    }
}
